<?php

/*
Snort Grok v1.1

Copyright 2004-2006 by Erik Wrenholt

Released under Perl Artistic License.  See LICENSE.txt

http://www.timestretch.com/site/snort_grok/

Simple PHP/MYSQL Snort IDS reports with a drill down interface. These
reports will work best on small databases.  For the best performance,
archive older data, remove noisy rules, delete false positives, and 
take the time to set the constants below.

You will need to install Snort and enable MySQL logging to use Snort Grok.
	
	Snort is available for download at http://www.snort.org/

Please send comments, feature requests, and bug reports
	erik -at- timestretch.com

PS: Be sure to backup your database before trying the delete command.
	mysqldump -u snort -p snort > snort.sql
	
Release Notes

1.1 	2006-05-16

		Fixed a bug that caused php to give a v4.2 global vars warning.

		Changed the defaults to improve performance on larger databases.

		Added Alerts by Dest. IP Report.
		
		Always show sensors now.
		
		Show portscans.
		
		Toggle DNS.
		
		Handle multiple sensors correctly.
		
		Indexed for faster speed.
		
		Fixed bugs on deleting.

*/


session_start();
$starttime = time();

//---------------------------------------------------------
// Configure MySQL with your snort username and password

/*

-- Paste these into mysql shell to create indexes to speed up the
-- queries below.  These are optional, but will greatly speed up the
-- reports.

use snort;
create index cid_index on iphdr (cid,sid);
create index cid_index on icmphdr (cid,sid);
create index cid_index on udphdr (cid,sid);
create index cid_index on tcphdr (cid,sid);
create index cid_index on event (cid,sid);

*/

$mysql_host = 'localhost';
$mysql_user = 'root';
$mysql_pass = '';
$mysql_database = 'snort';

define ("DEBUG", false);

// Default to Hex View when viewing payload
define ("DEFAULT_HEXVIEW", false);

define ("MAX_ROWS_PER_PAGE", 40);

define ("DNS_LOOKUPS", "off");
// How many rows of should we show dns names for?
// Set to 0 for fastest performance, or up to MAX_ROWS_PER_PAGE
define ("LOOK_UP_FIRST", 40);

//---------------------------------------------------------
// How many characters of the dns name should we show?
define ("MAX_DOMAINNAME_LEN", 25);

// if you want the unique ID # to appear next to each
// item in the table, set this true
define ("SHOW_ID_IN_TABLE", true);

//Maximum width of the chart in pixels
define ("MAX_GRAPH", 200);

// If your reports are taking too long, you can set
// the default view to a shorter time period...

// These can be 'hour', 'day', 'week', or 'all'
define ("DEFAULT_VIEW", 'hour');

// Show the text above the graph. 
// Default is true because it gives the row count
define ("SHOW_HELP_TEXT", true);

// If you want rwhois referrals shown, set this to true
define ("FOLLOW_RWHOIS", false);

define ("REPORT_NAME", "Snort Grok v1.1");

//---------------------------------------------------------
$dbh = mysql_pconnect($mysql_host,$mysql_user,$mysql_pass);

if (!$dbh)
	die ("Could not connect to database: $mysql_database.\n");
if (!mysql_select_db($mysql_database)) {
	die ("Could not select database: $mysql_database.\n");
}
//---------------------------------------------------------
function getmicrotime() { 
	list($usec, $sec) = explode(" ",microtime()); 
	return ((float)$usec + (float)$sec); 
} 
//---------------------------------------------------------
function query($query)
{
	
	if (DEBUG) {
		echo ($query);
		$time_start = getmicrotime();
	}
	$res = mysql_query($query) 
		or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") <span style=\"color:red;\">" . mysql_error())."</span>";

	if (DEBUG) {
		$time_end = getmicrotime();
		$time = $time_end - $time_start;
		printf("<p>%0.2f</p>", $time);
	}


	return $res;
}
//---------------------------------------------------------
// Our queries could take awhile.. 
// We'll just let them try to finish.
set_time_limit(0);

// Are we filtered in on specific events or IPs?
$dns_lookups = @$_GET['dns_lookups'];
$cid = @$_GET['cid'];
$ip_src = @$_GET['ip_src'];
$ip_dst = @$_GET['ip_dst'];
$sig_sid = @$_GET['sig_sid'];
$sport = @$_GET['sport'];
$dport = @$_GET['dport'];
$timestamp = @$_GET['timestamp'];
$row_start = @$_GET['row_start'];
$filter = @$_GET['filter'];
$type = @$_GET['type'];
$sid = @$_GET['sensor'];


if ($dns_lookups != '') {
	$_SESSION['dns_lookups'] = $dns_lookups;
} elseif (@$_SESSION['dns_lookups'] != '') {
	$dns_lookups = $_SESSION['dns_lookups'];
} else {
	$dns_lookups = DNS_LOOKUPS;	//default filtering period
}

if ($filter != '') {
	$_SESSION['filter'] = $filter;
} elseif (@$_SESSION['filter'] != '') {
	$filter = $_SESSION['filter'];
} else {
	$filter = DEFAULT_VIEW;	//default filtering period
}

if ($type != '') {
	$_SESSION['type'] = $type;
} elseif (@$_SESSION['type'] != '') {
	$type = $_SESSION['type'];
} else {
	$type = "tcp";	//default type of alert to show is tcp
}

if ($sid != '') {
	$_SESSION['sid'] = $sid;
} elseif (@$_SESSION['sid'] != '') {
	$sid = $_SESSION['sid'];
} else {
	$sid = "1";		//default sensor id 1.. if there is only one, don't show sensor tabs
}

$report_detail = '';
$WHERE = '';

if ($sid) {
	$WHERE .= " AND event.sid='$sid'";
}

switch ($filter) {
	case "hour":
		$WHERE .= " AND UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(event.timestamp) < 3600";
		break;
	case "2hours":
		$WHERE .= " AND UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(event.timestamp) < 7200";
		break;
	case "4hours":
		$WHERE .= " AND UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(event.timestamp) < 14400";
		break;
	case "12hours":
		$WHERE .= " AND UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(event.timestamp) < 43200";
		break;
	case "day":
		$WHERE .= " AND UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(event.timestamp) < 86400";
		break;
	case "week":
		$WHERE .= " AND UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(event.timestamp) < 604800";
		break;
	default:
	case "all":
		$WHERE .= "";
		break;
}

// Show the hourly graph when viewing a day

if ($filter == 'hour' || $filter == '2hours' || $filter == '4hours' || $filter == '12hours') {
	//Timestamp format for day hour...
	$ts_format = "%m-%d-%Y hour: %H";
	$period = "hour";
} else if ($filter == 'day') {
	//Timestamp format for day hour...
	$ts_format = "%m-%d-%Y hour: %H";
	$period = "hour";
}else {
	//Timestamp format for day...
	$ts_format = "%m-%d-%Y";
	$period = "day";
}

// TCP, UDP, ICMP events are logged in different tables...  
// Substitute correct column names

if ($type == 'tcp' || $type == '') {
	$type = 'tcp';
	
	$SELECT = "inet_ntoa(iphdr.ip_src) as ip_src,
	tcphdr.tcp_sport as sport,
	inet_ntoa(iphdr.ip_dst) as ip_dst,
	tcphdr.tcp_dport as dport
FROM event
JOIN iphdr ON event.cid=iphdr.cid and event.sid=iphdr.sid
JOIN tcphdr ON event.cid=tcphdr.cid and event.sid=tcphdr.sid
JOIN signature ON signature.sig_id=event.signature ";

	
	$report_name = "TCP Events Report";
	
	if ($sport != '') {
		$WHERE .= sprintf(" AND tcphdr.tcp_sport='%s' ", $sport);
		$report_detail .= ": Source Port $sport";
	}
	if ($dport != '') {
		$WHERE .= sprintf(" AND tcphdr.tcp_dport='%s' ", $dport);
		$report_detail .= ": Dest. Port $dport";
	}
	
} elseif ($type == "udp") {
	$type = 'udp';
	
	$SELECT = "inet_ntoa(iphdr.ip_src) as ip_src,
	udphdr.udp_sport as sport,
	inet_ntoa(iphdr.ip_dst) as ip_dst,
	udphdr.udp_dport as dport
FROM event
JOIN iphdr ON event.cid=iphdr.cid and event.sid=iphdr.sid
JOIN udphdr ON event.cid=udphdr.cid and event.sid=udphdr.sid
JOIN signature ON signature.sig_id=event.signature ";
	
	$report_name = "UDP Events Report";
	
	if ($sport != '') {
		$WHERE .= sprintf(" AND udphdr.udp_sport='%s' ", $sport);
		$report_detail .= ": Source Port $sport";
	}
	if ($dport != '') {
		$WHERE .= sprintf(" AND udphdr.udp_dport='%s' ", $dport);
		$report_detail .= ": Dest. Port $dport";
	}
	
} elseif ($type == "icmp") {
	$type = 'icmp';
	
	$SELECT = "inet_ntoa(iphdr.ip_src) as ip_src,
	icmphdr.icmp_type as icmptype,
	icmphdr.icmp_code as icmpcode,
	inet_ntoa(iphdr.ip_dst) as ip_dst
FROM event
JOIN iphdr ON event.cid=iphdr.cid and event.sid=iphdr.sid
JOIN icmphdr ON event.cid=icmphdr.cid and event.sid=icmphdr.sid
JOIN signature ON signature.sig_id=event.signature ";

	$report_name = "ICMP Events Report";
} else {
	$type = 'portscans';
	
	$SELECT = "inet_ntoa(iphdr.ip_src) as ip_src,
	inet_ntoa(iphdr.ip_dst) as ip_dst
FROM event
LEFT JOIN iphdr ON event.cid=iphdr.cid and event.sid=iphdr.sid
LEFT JOIN icmphdr ON event.cid=icmphdr.cid and event.sid=icmphdr.sid
LEFT JOIN udphdr ON event.cid=udphdr.cid and event.sid=udphdr.sid
LEFT JOIN tcphdr ON event.cid=tcphdr.cid and event.sid=tcphdr.sid 
LEFT JOIN signature ON signature.sig_id=event.signature ";
	
	$WHERE .= " AND icmphdr.cid IS NULL AND udphdr.cid IS NULL AND tcphdr.cid IS NULL ";
	$report_name = "Portscan Report";
}

if ($sig_sid != '') {
	$WHERE .= sprintf(" AND sig_sid='%s' ", $sig_sid);
	$report_detail .= ": Signature #$sig_sid";
}
if ($ip_src != '') {
	$WHERE .= sprintf(" AND inet_ntoa(iphdr.ip_src)='%s' ", $ip_src);
	$report_detail .= ": Src IP $ip_src";
}
if ($ip_dst != '') {
	$WHERE .= sprintf(" AND inet_ntoa(iphdr.ip_dst)='%s' ", $ip_dst);
	$report_detail .= ": Dst IP $ip_dst";
}
if ($timestamp != '') {
	$WHERE .= " AND DATE_FORMAT(event.timestamp,'$ts_format')='$timestamp' ";
	$report_detail .= ": Timestamp $timestamp";	
}
// Show only one event
if ($cid != '') {
	$WHERE = sprintf(" AND event.cid='%d' ", $cid);
	$report_detail .= ": #$cid";
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<title><?php echo $report_name." &mdash; ".REPORT_NAME; ?></title>
<style type="text/css">
body {
	font-family: verdana, helvetica;
	font-size: 12px;
	margin-top: 6px;
	margin-left: 16px;
	margin-right: 16px;
}
.tab {
	background-color: #EEE;
	padding: 6px;
	font-weight: bold;
}
.tab:hover {
	background-color: #DDD;
}
.selected {
	background-color: #000;
	color: #FFF;
	padding: 6px;
	font-weight: bold;
}
small .selected {
	background-color: blue;
}
.tabtray,.footer {
	background-color: #FFF;
	border-bottom: 4px solid #000;
	padding: 6px;
}

.tray,.footer {
	padding-top: 6px;
}
pre {
	font-family: monaco, courier;
	font-size: 9pt;
	margin-left: 1em;
}
p {
	margin-left: 1em;
}
table {
	font-size: 11px;
	margin-left: 1em;
}
th {
	font-weight: bold;
	font-size: 12px;
	background-color: #EEE;
	text-align: left;
}
.barchart {
	float: left;
	background-color: blue;
	color: red;
	height: 15px;
}
.inactive {
	color: #888;
}
</style>

<script language="JavaScript">
<!--
function confirmSubmit()
{

var agree=confirm("Are you sure you want to delete these records?");

if (agree)
	return true ;
else
	return false ;
}
// -->
</script>

</head>
<body>
<div>

<?php
$my_get_var_list = "&amp;sig_sid=$sig_sid&amp;ip_src=$ip_src&amp;ip_dst=$ip_dst&amp;dport=$dport&amp;sport=$sport";	//&amp;timestamp=$timestamp
?>

<small>
<a href="?dns_lookups=<?php if ($dns_lookups == "on") echo 'off'; else echo 'on';?><?php echo $my_get_var_list;?>" <?php if ($dns_lookups == "on") echo 'class="selected"'; else echo 'class="tab"';?> style="float:right;">DNS Lookups</a> 
<?php

	$query = "SELECT DISTINCT sid FROM event order by sid";
	$res = query($query);
	if ($res) {
		$row_count = mysql_num_rows ($res);
		
		//if ($row_count > 1) {
			while ($sensor_row = mysql_fetch_assoc($res)) {
				$row_sid = $sensor_row['sid'];
				if ($row_sid == $sid)
					echo "<a href=\"?sensor=$row_sid\" class=\"selected\">Sensor $row_sid</a> ";
				else
					echo "<a href=\"?sensor=$row_sid\" class=\"tab\">Sensor $row_sid</a> ";
			}
		//}
		
	} else {
		echo "No sensors found in database.";
	}

?>
</small>
</div><br />

<div class="tabtray">

<a href="?type=tcp" <?php if ($type=='tcp') echo 'class="selected"'; else echo 'class="tab"';?>>TCP Alerts</a>
<a href="?type=udp" <?php if ($type=='udp') echo 'class="selected"'; else echo 'class="tab"';?>>UDP Alerts</a> 
<a href="?type=icmp" <?php if ($type=='icmp') echo 'class="selected"'; else echo 'class="tab"';?>>ICMP Alerts</a>
<a href="?type=portscans" <?php if ($type=='portscans') echo 'class="selected"'; else echo 'class="tab"';?>>Portscans</a>
</div>


<div class="tray">
<small>Show alerts during 
<a href="?filter=hour<?php echo $my_get_var_list;?>" <?php if ($filter=='hour') echo 'class="selected"'; else echo 'class="tab"';?>>Past Hour</a> 
<a href="?filter=2hours<?php echo $my_get_var_list;?>" <?php if ($filter=='2hours') echo 'class="selected"'; else echo 'class="tab"';?>>Past 2 Hours</a> 
<a href="?filter=4hours<?php echo $my_get_var_list;?>" <?php if ($filter=='4hours') echo 'class="selected"'; else echo 'class="tab"';?>>Past 4 Hours</a> 
<a href="?filter=12hours<?php echo $my_get_var_list;?>" <?php if ($filter=='12hours') echo 'class="selected"'; else echo 'class="tab"';?>>Past 12 Hours</a> 
<a href="?filter=day<?php echo $my_get_var_list;?>" <?php if ($filter=='day') echo 'class="selected"'; else echo 'class="tab"';?>>Past Day</a> 
<a href="?filter=week<?php echo $my_get_var_list;?>" <?php if ($filter=='week') echo 'class="selected"'; else echo 'class="tab"';?>>Past Week</a> 
<a href="?filter=all<?php echo $my_get_var_list;?>" <?php if ($filter=='all') echo 'class="selected"'; else echo 'class="tab"';?>>All Records</a></small> </div>
<?php

//----------------------------------------------------------
// If we are deleting, lets do this first...

$action = @$_GET['action'];
if ($action == "delete") {
	
$query = "SELECT 
event.cid,
timestamp, 
sig_name,
signature.sig_sid,
$SELECT
WHERE 1 $WHERE
ORDER BY timestamp DESC";
	
	$res = query($query);
	
	if ($res) {
		$x = 0;
		$row_count = mysql_num_rows ($res);
		while ($row[++$x] = mysql_fetch_assoc($res)) {
			$cid_list .= $row[$x]['cid'].", ";
		}
		DeleteRows ($dbh, rtrim($cid_list,", "), $sid);
	}
}


//----------------------------------------------------------
// Total Events & Number of Unique Sigs

$query = "SELECT 
count(*) as the_count,
UNIX_TIMESTAMP(MIN(timestamp)) as the_min,
UNIX_TIMESTAMP(MAX(timestamp)) as the_max,
sig_name
FROM event,signature WHERE event.sid='$sid' AND signature=sig_id group by signature";

$res = query($query);

if ($res) {
	echo "<h3>Snort Database Overview</h3>";
	echo "<ul>";
	$sigs = mysql_num_rows($res);
	$total_alerts = 0;
	$max_time = 0;
	while ($row = mysql_fetch_assoc($res)) {
		if ($row['the_max'] > $max_time) {
			$max_time = $row['the_max'];
			$sig_name = $row['sig_name'];
		}
		$total_alerts += $row['the_count'];
	}
	echo "<li>$total_alerts total alerts.";
	if ($total_alerts > 0)
		printf ("  Most recent: %s %s ago. </li>", $sig_name,thatDate($max_time));
	else
		echo "</li>";
	echo "<li>$sigs unique signatures</li>";
	echo "</ul>";
}

flush();


if ($cid == '' && $action != "delete") {		// && $row_start == ''

	//----------------------------------------------------------
	// Do Quick Alerts by Day or hour Report
	
$query = "SELECT 
count(*) as the_count,
DATE_FORMAT(timestamp,'$ts_format') as timestamp_by_day, 
DATE_FORMAT(timestamp,'%W') as weekname, 
sig_name,
event.cid,
signature.sig_sid,
$SELECT
WHERE 1 $WHERE
GROUP BY timestamp_by_day 
ORDER BY timestamp DESC";
	
	//echo $query;
	
	$res = query($query);
	
	if ($res) {
	
		$line_count = mysql_num_rows($res);
		$capital_type = strtoupper($type);
		
		unset($row_array);
		$max = 0;
		$total_alerts = 0;
		$x = 0;
		while ($row_array[++$x] = mysql_fetch_assoc($res)) {
			$the_count = $row_array[$x]['the_count'];
			if ($the_count > $max)
				$max = $the_count;
			$total_alerts += $the_count;
		}
		
		echo "<h3>$capital_type Alerts by $period $report_detail</h3>";
if (SHOW_HELP_TEXT)
		echo "<p>$total_alerts alerts were detected in the $line_count ${period}s shown.  Click on a day for more details.</p>";
		echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"2\" summary=\"$capital_type Alerts by Day\">";
	
		foreach ($row_array as $row) {
			if (is_array ($row)) {
				$timestamp_by_day = $row['timestamp_by_day'];
				$the_count = $row['the_count'];
				if ($max > MAX_GRAPH)
					$bar_length = round($the_count*(MAX_GRAPH/$max));
				else
					$bar_length = round($the_count);
				$weekname = $row['weekname'];
				echo "<tr><td><b><a href=\"?timestamp=$timestamp_by_day\">$timestamp_by_day</a></b></td>
		<td><span class=\"barchart\" style=\"width:${bar_length}px\"></span>&nbsp;($the_count) $weekname</td></tr>";
			}
		}
		
		echo "</table>";
	}
	
	flush();
	
	//----------------------------------------------------------
	// Do Quick summary report of # of alerts by signature
	
$query = "SELECT 
count(*) as the_count,
timestamp, 
sig_name,
event.cid,
signature.sig_sid,
$SELECT
WHERE 1 $WHERE
GROUP BY sig_id
ORDER BY the_count DESC";
	
	//echo $query;
	
	$res = query($query);
	if ($res) {
		
		$line_count = mysql_num_rows($res);
		$capital_type = strtoupper($type);
		echo "<h3>Alerts per $capital_type Signature $report_detail</h3>";
if (SHOW_HELP_TEXT)
		echo "<p>$line_count $capital_type signatures shown.  
		Click on signature to filter.  
		Click on Snort Sid # to check Snort.org docs.</p>";
		echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"2\" summar=\"Alerts per $capital_type Signature\">";
		
		
		unset($row_array);
		$max = 0;
		$x = 0;
		while ($row_array[++$x] = mysql_fetch_assoc($res)) {
			$the_count = $row_array[$x]['the_count'];
			if ($the_count > $max)
				$max = $the_count;
		}
		
		foreach ($row_array as $row) {
			if (is_array ($row)) {
				$row_sig_name = size_string($row['sig_name'],50);
				$row_sig_sid = $row['sig_sid'];
				$the_count = $row['the_count'];
				if ($max > MAX_GRAPH)
					$bar_length = round($the_count*(MAX_GRAPH/$max));
				else
					$bar_length = round($the_count);
				echo "<tr><td><b><a href=\"?sig_sid=$row_sig_sid\">$row_sig_name</a></b> 
				<td>(<a href=\"http://www.snort.org/pub-bin/sigs.cgi?sid=$row_sig_sid\" target=\"-1\" style=\"cursor:help;\">Sid #$row_sig_sid</a>)</td>
				<td><span class=\"barchart\" style=\"width:${bar_length}px\"></span>&nbsp;($the_count)</td></tr>";
			}
		}
		
		echo "</table>";
	}
	
	flush();
	
	//----------------------------------------------------------
	// Do Quick summary report of # of alerts by source IP
	
$query = "SELECT 
count(*) as the_count,
ip_src, 
sig_name,
event.cid,
signature.sig_sid,
$SELECT
WHERE 1 $WHERE
GROUP BY ip_src
ORDER BY the_count DESC LIMIT 0,20";
	
	//echo $query;
	
	$res = query($query);
	
	if ($res) {
		
		$line_count = mysql_num_rows($res);
		$capital_type = strtoupper($type);
		echo "<h3>Top 20 Alerts by Source IP $report_detail</h3>";
if (SHOW_HELP_TEXT)
		echo "<p>$line_count IP addresses shown.  
		Click on IP to filter.</p>";
		echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"2\" summary=\"Alerts per $capital_type Signature\">";
		
		
		unset($row_array);
		$max = 0;
		$x = 0;
		while ($row_array[++$x] = mysql_fetch_assoc($res)) {
			$the_count = $row_array[$x]['the_count'];
			if ($the_count > $max)
				$max = $the_count;
		}
		
		foreach ($row_array as $row) {
			if (is_array ($row)) {
				$sig_name = $row['sig_name'];
			//	$sig_sid = $row['sig_sid'];
				$ip_src = $row['ip_src'];
				$the_count = $row['the_count'];
				if ($max > MAX_GRAPH)
					$bar_length = round($the_count*(MAX_GRAPH/$max));
				else
					$bar_length = round($the_count);
				
				if ($dns_lookups == "on" && preg_match("/^192/",$ip_src)) {
					$hostname = gethostbyaddr($ip_src);
					if ($hostname == $ip_src)
						$hostname = "-";
					else
						$hostname = "(<a href=\"?ip_src=$ip_src\">$hostname</a>)";
				} else {
						$hostname = "(<a href=\"?ip_src=$ip_src\">$ip_src</a>)";
				}
				echo "<tr><td><b><a href=\"?ip_src=$ip_src\">$ip_src</a></b></b> </td>
				<td>$hostname</td>
				<td><span class=\"barchart\" style=\"width:${bar_length}px\"></span>&nbsp;($the_count)</td></tr>";
			}
		}
		
		echo "</table>";
	}
	flush();
	//----------------------------------------------------------
	// Do Quick summary report of # of alerts by dest. IP
	
$query = "SELECT 
count(*) as the_count,
ip_dst, 
sig_name,
event.cid,
signature.sig_sid,
$SELECT
WHERE 1 $WHERE
GROUP BY ip_dst
ORDER BY the_count DESC LIMIT 0,20";
	
	//echo $query;
	
	$res = query($query);
	
	if ($res) {
		
		$line_count = mysql_num_rows($res);
		$capital_type = strtoupper($type);
		echo "<h3>Top 20 Alerts by Dest. IP $report_detail</h3>";
if (SHOW_HELP_TEXT)
		echo "<p>$line_count IP addresses shown.  
		Click on IP to filter.</p>";
		echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"2\" summary=\"Alerts per $capital_type Signature\">";
		
		
		unset($row_array);
		$max = 0;
		$x = 0;
		while ($row_array[++$x] = mysql_fetch_assoc($res)) {
			$the_count = $row_array[$x]['the_count'];
			if ($the_count > $max)
				$max = $the_count;
		}
		
		foreach ($row_array as $row) {
			if (is_array ($row)) {
				$sig_name = $row['sig_name'];
			//	$sig_sid = $row['sig_sid'];
				$ip_dst = $row['ip_dst'];
				$the_count = $row['the_count'];
				if ($max > MAX_GRAPH)
					$bar_length = round($the_count*(MAX_GRAPH/$max));
				else
					$bar_length = round($the_count);
				
				if ($dns_lookups == "on") {
					$hostname = gethostbyaddr($ip_dst);
					if ($hostname == $ip_dst)
						$hostname = "-";
					else
						$hostname = "(<a href=\"?ip_dst=$ip_dst\">$hostname</a>)";
				} else {
						$hostname = "(<a href=\"?ip_dst=$ip_dst\">$ip_dst</a>)";
				}
				echo "<tr><td><b><a href=\"?ip_dst=$ip_dst\">$ip_dst</a></b></b> </td>
				<td>$hostname</td>
				<td><span class=\"barchart\" style=\"width:${bar_length}px\"></span>&nbsp;($the_count)</td></tr>";
			}
		}
		
		echo "</table>";
	}
	flush();

} // end if... show above only if we aren't focused on a unique cid


if ($action != "delete") {
	//----------------------------------------------------------
	// Do table view of recent most signatures

$total_count = 0;

$query = "SELECT count(event.sid) as the_count,
$SELECT 
WHERE 1 $WHERE
GROUP BY event.sid";

	$res = query($query);
	if ($res) {
		$row = mysql_fetch_assoc ($res);
		if ($row) {
			$total_count = $row['the_count'];
		}
	}
//	echo "<h1>$total_count</h1>";

$max_rows = MAX_ROWS_PER_PAGE;

if (@$_GET['row_start'])
	$row_start = $_GET['row_start'];
else
	$row_start = 0;

$query = "SELECT 
event.cid,
timestamp, 
sig_name,
signature.sig_sid,
$SELECT
WHERE 1 $WHERE
ORDER BY timestamp DESC
LIMIT $row_start, $max_rows";
	
	//echo $query;
	
	$res = query($query);
	
	if ($res) {
		$x = 0;
		$row_count = mysql_num_rows ($res);
		$row = Array();
		while ($row[++$x] = mysql_fetch_assoc($res)) {
		}
		DrawTable($report_name.$report_detail, $type, $row, $total_count, $dns_lookups);
		
		if ($row_count == 1) {
			ScreenFalsePositive ($row);
		}
	} else {
		return "Nothing to report.  Are you sure Snort is configured to log to MySQL?";
	}
	
	flush();
}
//---------------------------------------------------------

// Print extra Payload data we captured for an individual event...

if ($cid != '') {
	$query = "SELECT data_payload FROM data where cid='$cid' and sid='$sid'";
	$res = query($query);
	if ($res) {
		$row_count = mysql_num_rows ($res);
		if ($row_count > 0)
			echo "<h3>Payload</h3>";
		
		while ($row = mysql_fetch_assoc($res)) {

			$binary_data = hex2bin($row['data_payload']);
			$bytes = strlen ($binary_data);
if (SHOW_HELP_TEXT)
			echo "<p><b>$bytes bytes captured</b>.";
			echo " <b>View as <a href=\"?cid=$cid&amp;view=hex\">Hex</a> or <a href=\"?cid=$cid&amp;view=ascii\">ASCII</a></b></p>";
			
			if ((DEFAULT_HEXVIEW && @$_GET['view'] == '') || @$_GET['view'] == "hex")
				echo '<pre>'.hexview($binary_data).'</pre>';
			else
				echo '<pre>'.htmlentities($binary_data).'</pre>';
		}
	} else {
		echo "No additional data was captured.";
	}

}

if (@$_GET['ip_src']) {
	quick_whois ($_GET['ip_src']);
}elseif (@$_GET['ip_dst']) {
	quick_whois ($_GET['ip_dst']);
}

//---------------------------------------------------------
function size_string ($str, $maxlen) {
	if (strlen($str) > $maxlen)
		$str = substr ($str, 0, $maxlen)."&#133;";
	return $str;
}
//---------------------------------------------------------
// Found this code somewhere on php.net.  
// Fixed a bug.. all floor functions were rounds.
function thatDate($time) { 
	$time=time()-$time; 

	$weeks=$time/604800; 
	$days=($time%604800)/86400; 
	$hours=(($time%604800)%86400)/3600; 
	$minutes=((($time%604800)%86400)%3600)/60; 
	$seconds=(((($time%604800)%86400)%3600)%60); 

	$timestring='['; 
	if (floor($days)) $timestring.=floor($days)."d "; 
	if (floor($hours)) $timestring.=floor($hours)."h "; 
	if (floor($minutes)) $timestring.=floor($minutes)."m"; 
	if (!floor($hours)&&!floor($days)) $timestring.=" ".floor($seconds)."s"; 
	$timestring.=']'; 

	return $timestring; 
}
//----------------------------------------------------------
function hexview($data){
	$bytePosition = $columnCount = $lineCount = 0;
	$columns = 16;
	$dataLength = strlen($data);
	//$return = array();
	$return_str = '<table border="0" cellspacing="0" cellpadding="2" summary=\"Payload Hex-Dump\">';
	for($n = 0; $n < $dataLength; $n++){
		$lines[$lineCount][$columnCount++] = substr($data, $n, 1);
		if($columnCount == $columns){
			$lineCount++;
			$columnCount = 0;
		}
	}
	foreach($lines as $line){
		$return_str .= '<tr><td align="right">'.$bytePosition.': </td><td>';
		for($n = 0; $n < $columns; $n++){
			$return_str .=  strtoupper(bin2hex(@$line[$n]));
			if (($n+1) % 4 == 0)
				$return_str .= " ";
		}
		$return_str .=  '</td><td>&nbsp;&nbsp;&nbsp;&nbsp;</td><td>';
		for($n = 0; $n < $columns; $n++){
			$ascii_id = ord(@$line[$n]);
			if ($ascii_id < 255 && $ascii_id > 32)
				$ascii_data = htmlentities($line[$n]);
			else
				$ascii_data = '&nbsp;';
			$return_str .=  $ascii_data;
		}
		$return_str .=  "</td></tr>\n";
		$bytePosition = $bytePosition + $columns;
	}
	$return_str .=  '</table>';
	return $return_str;
} 
//---------------------------------------------------------
function hex2bin($data)
{
	$len = strlen($data);
	return pack("H" . $len, $data);
}
//---------------------------------------------------------
// These might be redundant because they're in the even signature
// Make sure the type_names strings are aligned left so they can be parsed.

function GetICMPType ($type) {
$type_names = "0	Echo Reply
1	Unassigned
2	Unassigned
3	Destination Unreachable
4	Source Quench
5	Redirect
6	Alternate Host Address
7	Unassigned
8	Echo
9	Router Advertisement
10	Router Solicitation
11	Time Exceeded
12	Parameter Problem
13	Timestamp
14	Timestamp Reply
15	Information Request
16	Information Reply
17	Address Mask Request
18	Address Mask Reply
30	Traceroute
31	Datagram Conversion Error
32	Mobile Host Redirect
33	IPv6 Where-Are-You
34	IPv6 I-Am-Here
35	Mobile Registration Request
36	Mobile Registration Reply
37	Domain Name Request
38	Domain Name Reply
39	SKIP
40	Photuris";
	
	$types = split ("\n",$type_names);
	foreach ($types as $line) {
		$pair = split ("\t", $line);
		$key[$pair[0]] = $pair[1];
	}
	return $key[$type];
}
//---------------------------------------------------------
// These might be redundant because they're in the signature
// Make sure the type_names strings are aligned left so they can be parsed.
function GetICMPCode ($type, $code) {

switch ($type) {
	case "3":
$type_names="0	Net Unreachable
1	Host Unreachable
2	Protocol Unreachable
3	Port Unreachable
4	Fragmentation Needed and Don't Fragment was Set
5	Source Route Failed
6	Destination Network Unknown
7	Destination Host Unknown
8	Source Host Isolated
9	Communication with Destination Network is Administratively Prohibited
10	Communication with Destination Host is Administratively Prohibited
11	Destination Network Unreachable for Type of Service
12	Destination Host Unreachable for Type of Service
13	Communication Administratively Prohibited
14	Host Precedence Violation
15	Precedence cutoff in effect";
		break;
	case "5":
$type_names="0	Redirect Datagram for the Network (or subnet)
1	Redirect Datagram for the Host
2	Redirect Datagram for the Type of Service and Network
3	Redirect Datagram for the Type of Service and Host";	
		break;
	case "9":
$type_names="0	Normal router advertisement      
16	Does not route common traffic";	
		break;
	case "11":
$type_names="0	Time to Live exceeded in Transit
1	Fragment Reassembly Time Exceeded";	
		break;
	case "12":
$type_names="0	Pointer indicates the error
1	Missing a Required Option
2	Bad Length";	
		break;

}
	$types = split ("\n",$type_names);
	foreach ($types as $line) {
		$pair = split ("\t", $line);
		$key[$pair[0]] = $pair[1];
	}
	return $key[$code];
}

//---------------------------------------------------------
// Make nice easy to read labels for table headers from column names
function GetLabel ($key) {

	$array = array(
		"timestamp" => "Timestamp", 
		"sig_name" => "Event Signature", 
		"ip_src" => "Source IP", 
		"ip_dst" => "Dest. IP", 
		"sport" => "Source Port",
		"dport" => "Dest. Port",
		"icmptype" => "ICMP Type",
		"icmpcode" => "ICMP Code",
		"cid" => "ID"
	);
	return ($array[$key]);
}

//---------------------------------------------------------
function quick_whois ($target, $whoishost = NULL) {
	
	if (preg_match("/^(192|10|172)/",$target)) {
		print "<h3>The IP, $target, is reserved for special purposes.</h3>";
		return;
	}
	$port = 43;
	
	if ($whoishost == NULL) {
		$whoishost = "whois.arin.net";
	} else {
		// we got a referral to this server.  The could have passed a port #
		
		if (preg_match("/(.*):(\d+)(.*)/", $whoishost, $matches)) {
			$whoishost = $matches[1];
			$port = $matches[2];
		}
	}
	
	$errno_ptr = & $errno;
	$errstr_ptr = & $errstr;
	$whoissock = @fsockopen($whoishost, $port, $errno_ptr, $errstr, 5);
	
	if ($whoissock) {
		$out = "$target\n";
	
		fputs ($whoissock, $out);
		$whoisline = '';
		while (!feof($whoissock)) {
			$line = fgets($whoissock);		// Read until the end of the line
			if (preg_match ("/^ReferralServer\:\ (whois|rwhois):\/\/(.*)$/",$line, $ref_server)) {
				$type = $ref_server[1];
				$the_ref_server = $ref_server[2];

				if ($type == "rwhois" && FOLLOW_RWHOIS) {
					// Show the rwhois record with the rest of the data
					quick_whois ($target, $the_ref_server);
				} elseif ($type == "whois") {
					// We got referred so don't show the referring whois record and return.
					quick_whois ($target, $the_ref_server);
					return;
				}
				
			} elseif (preg_match ("/(.*) \((NET-.*)\)/s",$line, $ref_server)) {
				quick_whois ($ref_server[2]);
			}
			
			$whoisline .= $line;
		}
		fclose ($whoissock);
		echo "<h3>$whoishost:$port lookup: $target</h3>\n";
		echo "<pre>";
		echo htmlentities($whoisline);
		echo "</pre>";
		
	} else {
		echo "<h3>Could not check Whois with $whoishost.</h3>\n";
	}
}

//---------------------------------------------------------
function ScreenFalsePositive ($row) {
	//sids less than 100 are generated by the preprocessor...
	extract ($row[1]);
	if ($sig_sid > 100) {
		echo "<h3>Is this alert a false postive?</h3>";
		echo "<p>You can comment out the rule #$sig_sid or add one the following to your localhost.rules.</p>";
		echo "<blockquote><pre>";
		echo "# Suppress '$sig_name' with Source IP $ip_src\n";
		echo "suppress gen_id 1, sig_id $sig_sid, track by_src, ip $ip_src\n\n";
		echo "# Suppress '$sig_name' with Dest. IP $ip_dst\n";
		echo "suppress gen_id 1, sig_id $sig_sid, track by_dst, ip $ip_dst\n\n";
		echo "# Limit logging '$sig_name' to one event every 60 seconds\n";
		echo "threshold gen_id 1, sig_id $sig_sid, type limit, track by_src, count 1, seconds 60\n";
		echo "</pre></blockquote>";
	}
	//suppress gen_id 1, sig_id 618, track by_src, ip 130.239.18.160

	
}
//---------------------------------------------------------
function DeleteRows ($dbh, $cid_list, $sid) {
	$table_array = array(
		0 => "event",
		1 => "iphdr",
		2 => "tcphdr",
		3 => "udphdr",
		4 => "icmphdr",
		5 => "opt",
		6 => "data"
	);
	echo "<p>";
	foreach ($table_array as $table) {
		$query = "DELETE FROM $table WHERE sid='$sid' AND cid IN ($cid_list)";
		$res = query ($query, $dbh);
		if ($res) {
			$rows_affected = mysql_affected_rows();
			if ($rows_affected > 0)
				echo "Deleted ".$rows_affected." rows from $table.<br />";
		} else
			echo "Query Failed: ".mysql_error($res).".<br />";
	}
	echo "</p>";
}

//---------------------------------------------------------
function DrawTable ($report_title, $type, $row, $row_count, $dns_lookups) {

	if ($row_count <= 0)
		return;
	
	// How many dns names to lookup?
	$look_up_first = LOOK_UP_FIRST;
	// How many characters of the dns name should we show?
	$max_domainname_len = MAX_DOMAINNAME_LEN;
	// How many rows to display / page?
	$max_rows = MAX_ROWS_PER_PAGE;
	
	$fields = count(@$row[0]);
		
	echo "<h3>$report_title</h3>";
	
	//The ugly @ signs prevent errors from being generated
	
	if (@$_GET['row_start'])
		$row_start = $_GET['row_start'];
	else
		$row_start = 1;

	$cid = @$_GET['cid'];
	$sig_sid = @$_GET['sig_sid'];
	$timestamp = @$_GET['timestamp'];
	$ip_src = @$_GET['ip_src'];
	$ip_dst = @$_GET['ip_dst'];
	$sport = @$_GET['sport'];
	$dport = @$_GET['dport'];

	echo "<p>Click on an Event Signature for host details and payload.  Click on a host to see it's whois record.</p>";

	// This shows the back / next buttons
	// Inactive buttons are grayed-out so it's less jarring to the user
	
	if ($row_count > $max_rows) {
		echo "<p align=\"left\"><b>";
		
		$next_start = $row_start + $max_rows;
		if ($next_start > $row_count)
			$next_start = $row_count;
		
		if ($row_start > $max_rows)
			echo "<a href=\"?sig_sid=$sig_sid&amp;timestamp=$timestamp&amp;ip_src=$ip_src&amp;ip_dst=$ip_dst&amp;row_start=".
				($row_start-$max_rows)."\">[&lt;&lt; Back ]</a> ";
		else
			echo "<span class=\"inactive\">[&lt;&lt; Back ]</span> ";

		printf ("</b> Showing rows %d-$next_start of %d events. <b>",$row_start,$row_count);

		if ($next_start < $row_count)
			echo "<a href=\"?sig_sid=$sig_sid&amp;timestamp=$timestamp&amp;ip_src=$ip_src&amp;ip_dst=$ip_dst&amp;dport=$dport&amp;sport=$sport&amp;row_start=$next_start\">[ Next &gt;&gt;]</a>";
		else
			echo "<span class=\"inactive\">[ Next &gt;&gt;]</span> ";

		echo "</b></p>";
		$row_stop = $row_start + $max_rows;
	} else {
		printf ("<p> Showing %s events. </p>",$row_count);
		$row_stop = $row_count;
	}
	
	
	// Print headers
	echo "<table border=\"1\" cellpadding=\"4\" cellspacing=\"0\" summary=\"$report_title\" width=\"100%\"><tr>\n";
	foreach ($row[1] as $key=>$val) {
		if (SHOW_ID_IN_TABLE && $key == 'cid')
			echo sprintf("<th>%s</th>", GetLabel($key));
		elseif ($key != 'sig_sid' && $key != 'cid')
			echo sprintf("<th>%s</th>", GetLabel($key));
	}
	echo "</tr>\n";
	
	$row_counter = 0;
	// loop through and display details of each row
	for ($x = 0; $x <= $row_stop; $x++) {
		$row_counter++;
		echo "<tr>\n";
		
		foreach ($row[$x] as $key=>$val) {
			if ($key == 'ip_src' || $key == 'ip_dst') {
				$ip = $val;
				
				if ($row_count == 1 || $row_counter < $look_up_first) {
					if ($dns_lookups == "on")
						$hostname = gethostbyaddr($val);
					else
						$hostname = $val;
				} else
					$hostname = '';
				// Add elipses (...) before the hostname if it's too long
				if ($row_count != 1 && strlen($hostname) > $max_domainname_len+1)
					$hostname = "&#133;".substr ($hostname, -$max_domainname_len);

				if ($row_count == 1 || $hostname != $ip && $hostname != '')
					$val = $val."<br /><small><b>".$hostname."</b></small>";
				echo sprintf("<td><a href=\"?$key=%s\">$val</a></td>\n", $ip);
			//} elseif ($key == 'ip_dst') {
			//	$ip = $val;
			//	if ($row_count == 1)
			//		$val = $val."/<small><b>".gethostbyaddr($val)."</b></small>";
			//	echo sprintf("<td><a href=\"?ip_dst=%s\">$val</a></td>\n", $ip);
			} elseif ($key == 'sig_name') {
				echo sprintf("<td><a href=\"?cid=%d\">$val</a><br /></td>\n", $row[$x]['cid']);
			} elseif ($key == 'dport') {
				echo sprintf("<td><a href=\"?dport=%s\">$val</a><br /></td>\n", $row[$x]['dport']);
			} elseif ($key == 'sport') {
				echo sprintf("<td><a href=\"?sport=%d\">$val</a><br /></td>\n", $row[$x]['sport']);
			} elseif ($key == 'icmptype') {
				echo sprintf ("<td>%d (%s)</td>", $val, GetICMPType($val));
			} elseif ($key == 'icmpcode') {
				// ICMP codes appear in the signature description, so this appears to be redundant.
				//$icmpcode = GetICMPCode($row[$x]['icmptype'],$val);
				//if ($icmpcode != '')
				//	$icmpcode = " (".$icmpcode.")";
				echo "<td>$val</td>\n";
			} elseif ((SHOW_ID_IN_TABLE && $key == 'cid') && $key != 'sig_sid') {	//$key != 'cid' &&
				echo "<td>$val</td>\n";
			} elseif ($key != 'sig_sid' && $key != 'cid') {
				echo "<td>$val</td>\n";
			}
		}
		echo "</tr>\n";
	}
	echo "</table>\n";
	//Show Delete Query
	printf ("<p><b><a href=\"?type=$type&amp;sig_sid=$sig_sid&amp;timestamp=$timestamp&amp;ip_src=$ip_src&amp;ip_dst=$ip_dst&amp;dport=$dport&amp;sport=$sport&amp;row_start=$row_start&amp;cid=$cid&amp;action=delete\"
	onClick=\"return confirmSubmit()\">[Delete All %s Matching Events]</a></b></p>",$row_count);
	
}

?>
<div class="footer" align="right">
<?php
printf ("Generated in %d seconds\n", time() - $starttime);
$starttime = time();
?>
&bull; <a href="http://www.timestretch.com/site/snort_grok/"><?php echo REPORT_NAME; ?></a> 
&bull; by <a href="mailto:erik@timestretch.com">Erik Wrenholt</a>

</div>
</body>
</html>
